﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class TaskDetailDA : ITaskDetailDA
    {
        private string CASE_ID = "@CASE_ID";
        private string CREATED_BY = "@CREATED_BY";
        private string DOC_ID = "@DOC_ID";
        private string DOC_LINE_ID = "@DOC_LINE_ID";
        private string DOOR = "@DOOR";
        private string END_TIME = "@END_TIME";
        private string FINAL_TO_LOC = "@FINAL_TO_LOC";
        private string FROM_LOC = "@FROM_LOC";
        private string FROM_LPN_ID = "@FROM_LPN_ID";
        private string FROM_TRANS_ID = "@FROM_TRANS_ID";
        private string HOLD_ID = "@HOLD_ID";
        private string LIST_ID = "@LIST_ID";
        private string LOGICAL_FROM_LOC = "@LOGICAL_FROM_LOC";
        private string LOGICAL_TO_LOC = "@LOGICAL_TO_LOC";
        private string LOT = "@LOT";
        private string MESSAGE01 = "@MESSAGE01";
        private string MESSAGE02 = "@MESSAGE02";
        private string MESSAGE03 = "@MESSAGE03";
        private string OPT_BATCH_ID = "@OPT_BATCH_ID";
        private string OPT_LOCATION_TYPE = "@OPT_LOCATION_TYPE";
        private string OPT_QTY_LOC_LIMIT = "@OPT_QTY_LOC_LIMIT";
        private string OPT_QTY_LOC_MINIMUM = "@OPT_QTY_LOC_MINIMUM";
        private string OPT_REPLENISHMENT_UOM = "@OPT_REPLENISHMENT_UOM";
        private string OPT_TASK_SEQUENCE = "@OPT_TASK_SEQUENCE";
        private string ORDER_ID = "@ORDER_ID";
        private string ORDER_LINE_ID = "@ORDER_LINE_ID";
        private string OWNER_ID = "@OWNER_ID";
        private string PICK_DETAIL_ID = "@PICK_DETAIL_ID";
        private string PICK_METHOD = "@PICK_METHOD";
        private string PRIORITY = "@PRIORITY";
        private string QTY = "@QTY";
        private string REASON_ID = "@REASON_ID";
        private string RELEASE_DATE = "@RELEASE_DATE";
        private string ROUTE = "@ROUTE";
        private string SEQ_NO = "@SEQ_NO";
        private string SKU_ID = "@SKU_ID";
        private string SOURCE_PRIORITY = "@SOURCE_PRIORITY";
        private string SOURCE_TYPE = "@SOURCE_TYPE";
        private string SQL_CHECK_TASK_DETAIL_ID_UNIQUE = " SELECT COUNT(1) FROM @_@TASK_DETAIL WHERE TASK_DETAIL_ID = @TASK_DETAIL_ID ";
        private string SQL_DELETE_TASK_DETAIL = " DELETE FROM @_@TASK_DETAIL WHERE TASK_DETAIL_ID = @TASK_DETAIL_ID ";
        private string SQL_DELETE_TASK_DETAIL_BY_DOC_ID = " DELETE FROM @_@TASK_DETAIL WHERE DOC_ID = @DOC_ID ";
        private string SQL_GET_TASK_DETAIL_BY_DOC_AND_LINE_ID = "";
        private string SQL_INSERT_TASK_DETAIL = " INSERT INTO @_@TASK_DETAIL ( WH_ID, TASK_DETAIL_ID, TASK_TYPE, OWNER_ID, SKU_ID, LOT, UOM_ID, UOM_QTY, QTY, FROM_LOC, LOGICAL_FROM_LOC, FROM_LPN_ID, TO_LOC, LOGICAL_TO_LOC, TO_LPN_ID, CASE_ID, PICK_METHOD, STATUS, STATUS_MSG, PRIORITY, SOURCE_PRIORITY, HOLD_ID, USER_ID, USER_POSITION, USER_ID_OVERRIDE, START_TIME, END_TIME, SOURCE_TYPE, DOC_ID, DOC_LINE_ID, PICK_DETAIL_ID, ORDER_ID, ORDER_LINE_ID, LIST_ID, WAVE_ID, REASON_ID, MESSAGE01, MESSAGE02, MESSAGE03, FINAL_TO_LOC, RELEASE_DATE, OPT_BATCH_ID, OPT_TASK_SEQUENCE, OPT_REPLENISHMENT_UOM, OPT_QTY_LOC_MINIMUM, OPT_LOCATION_TYPE, OPT_QTY_LOC_LIMIT, SEQ_NO, DOOR, ROUTE, STOP, ZONE_ID, FROM_TRANS_ID, TO_TRANS_ID, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @TASK_DETAIL_ID, @TASK_TYPE, @OWNER_ID, @SKU_ID, @LOT, @UOM_ID, @UOM_QTY, @QTY, @FROM_LOC, @LOGICAL_FROM_LOC, @FROM_LPN_ID, @TO_LOC, @LOGICAL_TO_LOC, @TO_LPN_ID, @CASE_ID, @PICK_METHOD, @STATUS, @STATUS_MSG, @PRIORITY, @SOURCE_PRIORITY, @HOLD_ID, @USER_ID, @USER_POSITION, @USER_ID_OVERRIDE, @START_TIME, @END_TIME, @SOURCE_TYPE, @DOC_ID, @DOC_LINE_ID, @PICK_DETAIL_ID, @ORDER_ID, @ORDER_LINE_ID, @LIST_ID, @WAVE_ID, @REASON_ID, @MESSAGE01, @MESSAGE02, @MESSAGE03, @FINAL_TO_LOC, @RELEASE_DATE, @OPT_BATCH_ID, @OPT_TASK_SEQUENCE, @OPT_REPLENISHMENT_UOM, @OPT_QTY_LOC_MINIMUM, @OPT_LOCATION_TYPE, @OPT_QTY_LOC_LIMIT, @SEQ_NO, @DOOR, @ROUTE, @STOP, @ZONE_ID, @FROM_TRANS_ID, @TO_TRANS_ID, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_TASK_DETAIL = " SELECT  TD.ROWID AS ROWID, TD.WH_ID AS WH_ID, TD.TASK_DETAIL_ID AS TASK_DETAIL_ID, TD.TASK_TYPE AS TASK_TYPE, T2.[TEXT] AS TASK_TYPE_D, TD.OWNER_ID AS OWNER_ID, TD.SKU_ID AS SKU_ID, TD.LOT AS LOT, TD.UOM_ID AS UOM_ID,  TD.UOM_QTY AS UOM_QTY, TD.QTY AS QTY, TD.FROM_LOC AS FROM_LOC, TD.LOGICAL_FROM_LOC AS LOGICAL_FROM_LOC, TD.FROM_LPN_ID AS FROM_LPN_ID, TD.TO_LOC AS TO_LOC, TD.LOGICAL_TO_LOC AS LOGICAL_TO_LOC, TD.TO_LPN_ID AS TO_LPN_ID, TD.CASE_ID AS CASE_ID,  TD.PICK_METHOD AS PICK_METHOD, TD.STATUS AS STATUS, T.[TEXT] AS STATUS_D, TD.STATUS_MSG AS STATUS_MSG, TD.PRIORITY AS PRIORITY, TD.SOURCE_PRIORITY AS SOURCE_PRIORITY, TD.HOLD_ID AS HOLD_ID, TD.USER_ID AS USER_ID, TD.USER_POSITION AS USER_POSITION, TD.USER_ID_OVERRIDE AS USER_ID_OVERRIDE,  TD.START_TIME AS START_TIME, TD.END_TIME AS END_TIME, TD.SOURCE_TYPE AS SOURCE_TYPE, TD.DOC_ID AS DOC_ID, TD.DOC_LINE_ID AS DOC_LINE_ID, TD.PICK_DETAIL_ID AS PICK_DETAIL_ID, TD.ORDER_ID AS ORDER_ID, TD.ORDER_LINE_ID AS ORDER_LINE_ID, TD.LIST_ID AS LIST_ID,  TD.WAVE_ID AS WAVE_ID, TD.REASON_ID AS REASON_ID, TD.MESSAGE01 AS MESSAGE01, TD.MESSAGE02 AS MESSAGE02, TD.MESSAGE03 AS MESSAGE03, TD.FINAL_TO_LOC AS FINAL_TO_LOC, TD.RELEASE_DATE AS RELEASE_DATE, TD.OPT_BATCH_ID AS OPT_BATCH_ID, TD.OPT_TASK_SEQUENCE AS OPT_TASK_SEQUENCE,  TD.OPT_REPLENISHMENT_UOM AS OPT_REPLENISHMENT_UOM, TD.OPT_QTY_LOC_MINIMUM AS OPT_QTY_LOC_MINIMUM, TD.OPT_LOCATION_TYPE AS OPT_LOCATION_TYPE, TD.OPT_QTY_LOC_LIMIT AS OPT_QTY_LOC_LIMIT, TD.SEQ_NO AS SEQ_NO, TD.DOOR AS DOOR, TD.ROUTE AS ROUTE, TD.STOP AS STOP, TD.ZONE_ID AS ZONE_ID,  TD.FROM_TRANS_ID AS FROM_TRANS_ID, TD.TO_TRANS_ID AS TO_TRANS_ID, TD.TS AS TS, TD.CREATED_BY AS CREATED_BY, TD.CREATED_DATE AS CREATED_DATE, TD.UPDATED_BY AS UPDATED_BY, TD.UPDATED_DATE  AS UPDATED_DATE  FROM  @_@TASK_DETAIL TD  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = TD.STATUS AND SCD.SYS_CODE_TYPE = 'TaskStatus' LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = TD.TASK_TYPE AND SCD2.SYS_CODE_TYPE = 'TaskType' LEFT JOIN sysadm.TRANSLATION T ON 'TaskStatus.'+TD.STATUS = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN sysadm.TRANSLATION T2 ON 'TaskType.'+TD.TASK_TYPE = T2.ITEM_ID  \t\t\tAND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON TD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_UPDATE_TASK_DETAIL = " UPDATE @_@TASK_DETAIL SET WH_ID = @WH_ID, TASK_TYPE = @TASK_TYPE, OWNER_ID = @OWNER_ID, SKU_ID = @SKU_ID, LOT = @LOT, UOM_ID = @UOM_ID, UOM_QTY = @UOM_QTY, QTY = @QTY, FROM_LOC = @FROM_LOC, LOGICAL_FROM_LOC = @LOGICAL_FROM_LOC, FROM_LPN_ID = @FROM_LPN_ID, TO_LOC = @TO_LOC, LOGICAL_TO_LOC = @LOGICAL_TO_LOC, TO_LPN_ID = @TO_LPN_ID, CASE_ID = @CASE_ID, PICK_METHOD = @PICK_METHOD, STATUS = @STATUS, STATUS_MSG = @STATUS_MSG, PRIORITY = @PRIORITY, SOURCE_PRIORITY = @SOURCE_PRIORITY, HOLD_ID = @HOLD_ID, USER_ID = @USER_ID, USER_POSITION = @USER_POSITION, USER_ID_OVERRIDE = @USER_ID_OVERRIDE, START_TIME = @START_TIME, END_TIME = @END_TIME, SOURCE_TYPE = @SOURCE_TYPE, DOC_ID = @DOC_ID, DOC_LINE_ID = @DOC_LINE_ID, PICK_DETAIL_ID = @PICK_DETAIL_ID, ORDER_ID = @ORDER_ID, ORDER_LINE_ID = @ORDER_LINE_ID, LIST_ID = @LIST_ID, WAVE_ID = @WAVE_ID, REASON_ID = @REASON_ID, MESSAGE01 = @MESSAGE01, MESSAGE02 = @MESSAGE02, MESSAGE03 = @MESSAGE03, FINAL_TO_LOC = @FINAL_TO_LOC, RELEASE_DATE = @RELEASE_DATE, OPT_BATCH_ID = @OPT_BATCH_ID, OPT_TASK_SEQUENCE = @OPT_TASK_SEQUENCE, OPT_REPLENISHMENT_UOM = @OPT_REPLENISHMENT_UOM, OPT_QTY_LOC_MINIMUM = @OPT_QTY_LOC_MINIMUM, OPT_LOCATION_TYPE = @OPT_LOCATION_TYPE, OPT_QTY_LOC_LIMIT = @OPT_QTY_LOC_LIMIT, SEQ_NO = @SEQ_NO, DOOR = @DOOR, ROUTE = @ROUTE, STOP = @STOP, ZONE_ID = @ZONE_ID, FROM_TRANS_ID = @FROM_TRANS_ID, TO_TRANS_ID = @TO_TRANS_ID, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE TASK_DETAIL_ID = @TASK_DETAIL_ID  ";
        private string SQL_UPDATE_TASK_DETAIL_STATUS = " UPDATE @_@TASK_DETAIL SET STATUS = @STATUS, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE PICK_DETAIL_ID = @PICK_DETAIL_ID AND TASK_TYPE = 'PK' ";
        private string SQL_UPDATE_TASK_DETAIL_STATUS_BY_DOC_ID = " UPDATE @_@TASK_DETAIL SET STATUS = @STATUS, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE DOC_ID = @DOC_ID AND TASK_TYPE = @TASK_TYPE";
        private string SQL_UPDATE_TASK_DETAIL_STATUS_BY_DOC_ID_AND_LINE_ID = " UPDATE @_@TASK_DETAIL SET STATUS = @STATUS, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE DOC_ID = @DOC_ID AND DOC_LINE_ID = @DOC_LINE_ID AND TASK_TYPE = @TASK_TYPE";
        private string SQL_UPDATE_TASK_DETAIL_STATUS_BY_PC = " UPDATE @_@TASK_DETAIL SET STATUS = @STATUS, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE DOC_ID = @DOC_ID AND DOC_LINE_ID = @DOC_LINE_ID AND TASK_TYPE = @TASK_TYPE ";
        private string START_TIME = "@START_TIME";
        private string STATUS = "@STATUS";
        private string STATUS_MSG = "@STATUS_MSG";
        private string STOP = "@STOP";
        private string TASK_DETAIL_ID = "@TASK_DETAIL_ID";
        private string TASK_TYPE = "@TASK_TYPE";
        private string TO_LOC = "@TO_LOC";
        private string TO_LPN_ID = "@TO_LPN_ID";
        private string TO_TRANS_ID = "@TO_TRANS_ID";
        private string TS = "@TS";
        private string UOM_ID = "@UOM_ID";
        private string UOM_QTY = "@UOM_QTY";
        private string UPDATED_BY = "@UPDATED_BY";
        private string USER_ID = "@USER_ID";
        private string USER_ID_OVERRIDE = "@USER_ID_OVERRIDE";
        private string USER_POSITION = "@USER_POSITION";
        private string WAVE_ID = "@WAVE_ID";
        private string WH_ID = "@WH_ID";
        private string ZONE_ID = "@ZONE_ID";

        public bool CheckTaskDetailIDUnique(string taskDetailID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TASK_DETAIL_ID", taskDetailID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_TASK_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckTaskDetailIDUnique(DataBase dataBase, DbTransaction tran, string taskDetailID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TASK_DETAIL_ID", taskDetailID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_TASK_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteTaskDetail(List<string> taskDetailID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int j = 0; j < taskDetailID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TASK_DETAIL_ID", taskDetailID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_TASK_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteTaskDetail(DataBase dataBase, DbTransaction tran, List<string> taskDetailID, string whLoginID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < taskDetailID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TASK_DETAIL_ID", taskDetailID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_TASK_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteTaskDetailByDocID(DataBase dataBase, DbTransaction tran, string DocID, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DOC_ID", DocID) };
                result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_TASK_DETAIL_BY_DOC_ID.Replace("@_@", whLoginID), paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllTaskDetail(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_TASK_DETAIL.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetAllTaskDetail(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_TASK_DETAIL.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public List<TaskDetailInfo> GetTaskDetailByFromTransID(string fromTransID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_TASK_DETAIL.Replace("@_@", whLoginID) + " WHERE FROM_TRANS_ID = @FROM_TRANS_ID AND STATUS = 'Pending' ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@FROM_TRANS_ID", fromTransID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<TaskDetailInfo> taskDetailInfos = new List<TaskDetailInfo>();
            TaskDetailInfo taskDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    taskDetailInfo = new TaskDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["TASK_DETAIL_ID"].ToString(), reader["TASK_TYPE"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOT"].ToString(), reader["UOM_ID"].ToString(), (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["FROM_LOC"].ToString(), reader["LOGICAL_FROM_LOC"].ToString(), reader["FROM_LPN_ID"].ToString(), reader["TO_LOC"].ToString(), reader["LOGICAL_TO_LOC"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["PICK_METHOD"].ToString(), reader["STATUS"].ToString(), reader["STATUS_MSG"].ToString(), reader["PRIORITY"].ToString(), reader["SOURCE_PRIORITY"].ToString(), reader["HOLD_ID"].ToString(), reader["USER_ID"].ToString(), reader["USER_POSITION"].ToString(), reader["USER_ID_OVERRIDE"].ToString(), (reader["START_TIME"].ToString() != "") ? DateTime.Parse(reader["START_TIME"].ToString()) : (CS00002 = new DateTime()), (reader["END_TIME"].ToString() != "") ? DateTime.Parse(reader["END_TIME"].ToString()) : (CS00002 = new DateTime()), reader["SOURCE_TYPE"].ToString(), reader["DOC_ID"].ToString(), (reader["DOC_LINE_ID"].ToString() != "") ? int.Parse(reader["DOC_LINE_ID"].ToString()) : 0, reader["PICK_DETAIL_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["LIST_ID"].ToString(), reader["WAVE_ID"].ToString(), reader["REASON_ID"].ToString(), reader["MESSAGE01"].ToString(), reader["MESSAGE02"].ToString(), reader["MESSAGE03"].ToString(), reader["FINAL_TO_LOC"].ToString(), (reader["RELEASE_DATE"].ToString() != "") ? DateTime.Parse(reader["RELEASE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["OPT_BATCH_ID"].ToString(), reader["OPT_TASK_SEQUENCE"].ToString(), reader["OPT_REPLENISHMENT_UOM"].ToString(), (reader["OPT_QTY_LOC_MINIMUM"].ToString() != "") ? decimal.Parse(reader["OPT_QTY_LOC_MINIMUM"].ToString()) : 0M, reader["OPT_LOCATION_TYPE"].ToString(), (reader["OPT_QTY_LOC_LIMIT"].ToString() != "") ? decimal.Parse(reader["OPT_QTY_LOC_LIMIT"].ToString()) : 0M, (reader["SEQ_NO"].ToString() != "") ? int.Parse(reader["SEQ_NO"].ToString()) : 0, reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["STOP"].ToString(), reader["ZONE_ID"].ToString(), reader["FROM_TRANS_ID"].ToString(), reader["TO_TRANS_ID"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["TASK_TYPE_D"].ToString(), reader["STATUS_D"].ToString());
                    taskDetailInfos.Add(taskDetailInfo);
                }
            }
            return taskDetailInfos;
        }

        public List<TaskDetailInfo> GetTaskDetailByFromTransID(DataBase dataBase, DbTransaction tran, string fromTransID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_TASK_DETAIL.Replace("@_@", whLoginID) + " WHERE FROM_TRANS_ID = @FROM_TRANS_ID AND STATUS = 'Pending' ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@FROM_TRANS_ID", fromTransID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<TaskDetailInfo> taskDetailInfos = new List<TaskDetailInfo>();
            TaskDetailInfo taskDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                taskDetailInfo = new TaskDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["TASK_DETAIL_ID"].ToString(), reader["TASK_TYPE"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOT"].ToString(), reader["UOM_ID"].ToString(), (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["FROM_LOC"].ToString(), reader["LOGICAL_FROM_LOC"].ToString(), reader["FROM_LPN_ID"].ToString(), reader["TO_LOC"].ToString(), reader["LOGICAL_TO_LOC"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["PICK_METHOD"].ToString(), reader["STATUS"].ToString(), reader["STATUS_MSG"].ToString(), reader["PRIORITY"].ToString(), reader["SOURCE_PRIORITY"].ToString(), reader["HOLD_ID"].ToString(), reader["USER_ID"].ToString(), reader["USER_POSITION"].ToString(), reader["USER_ID_OVERRIDE"].ToString(), (reader["START_TIME"].ToString() != "") ? DateTime.Parse(reader["START_TIME"].ToString()) : (CS00002 = new DateTime()), (reader["END_TIME"].ToString() != "") ? DateTime.Parse(reader["END_TIME"].ToString()) : (CS00002 = new DateTime()), reader["SOURCE_TYPE"].ToString(), reader["DOC_ID"].ToString(), (reader["DOC_LINE_ID"].ToString() != "") ? int.Parse(reader["DOC_LINE_ID"].ToString()) : 0, reader["PICK_DETAIL_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["LIST_ID"].ToString(), reader["WAVE_ID"].ToString(), reader["REASON_ID"].ToString(), reader["MESSAGE01"].ToString(), reader["MESSAGE02"].ToString(), reader["MESSAGE03"].ToString(), reader["FINAL_TO_LOC"].ToString(), (reader["RELEASE_DATE"].ToString() != "") ? DateTime.Parse(reader["RELEASE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["OPT_BATCH_ID"].ToString(), reader["OPT_TASK_SEQUENCE"].ToString(), reader["OPT_REPLENISHMENT_UOM"].ToString(), (reader["OPT_QTY_LOC_MINIMUM"].ToString() != "") ? decimal.Parse(reader["OPT_QTY_LOC_MINIMUM"].ToString()) : 0M, reader["OPT_LOCATION_TYPE"].ToString(), (reader["OPT_QTY_LOC_LIMIT"].ToString() != "") ? decimal.Parse(reader["OPT_QTY_LOC_LIMIT"].ToString()) : 0M, (reader["SEQ_NO"].ToString() != "") ? int.Parse(reader["SEQ_NO"].ToString()) : 0, reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["STOP"].ToString(), reader["ZONE_ID"].ToString(), reader["FROM_TRANS_ID"].ToString(), reader["TO_TRANS_ID"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["TASK_TYPE_D"].ToString(), reader["STATUS_D"].ToString());
                taskDetailInfos.Add(taskDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return taskDetailInfos;
        }

        public TaskDetailInfo GetTaskDetailByID(string taskdetailID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_TASK_DETAIL.Replace("@_@", whLoginID) + " WHERE TASK_DETAIL_ID = @TASK_DETAIL_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TASK_DETAIL_ID", taskdetailID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            TaskDetailInfo taskDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    taskDetailInfo = new TaskDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["TASK_DETAIL_ID"].ToString(), reader["TASK_TYPE"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOT"].ToString(), reader["UOM_ID"].ToString(), (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["FROM_LOC"].ToString(), reader["LOGICAL_FROM_LOC"].ToString(), reader["FROM_LPN_ID"].ToString(), reader["TO_LOC"].ToString(), reader["LOGICAL_TO_LOC"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["PICK_METHOD"].ToString(), reader["STATUS"].ToString(), reader["STATUS_MSG"].ToString(), reader["PRIORITY"].ToString(), reader["SOURCE_PRIORITY"].ToString(), reader["HOLD_ID"].ToString(), reader["USER_ID"].ToString(), reader["USER_POSITION"].ToString(), reader["USER_ID_OVERRIDE"].ToString(), (reader["START_TIME"].ToString() != "") ? DateTime.Parse(reader["START_TIME"].ToString()) : (CS00003 = new DateTime()), (reader["END_TIME"].ToString() != "") ? DateTime.Parse(reader["END_TIME"].ToString()) : (CS00003 = new DateTime()), reader["SOURCE_TYPE"].ToString(), reader["DOC_ID"].ToString(), (reader["DOC_LINE_ID"].ToString() != "") ? int.Parse(reader["DOC_LINE_ID"].ToString()) : 0, reader["PICK_DETAIL_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["LIST_ID"].ToString(), reader["WAVE_ID"].ToString(), reader["REASON_ID"].ToString(), reader["MESSAGE01"].ToString(), reader["MESSAGE02"].ToString(), reader["MESSAGE03"].ToString(), reader["FINAL_TO_LOC"].ToString(), (reader["RELEASE_DATE"].ToString() != "") ? DateTime.Parse(reader["RELEASE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["OPT_BATCH_ID"].ToString(), reader["OPT_TASK_SEQUENCE"].ToString(), reader["OPT_REPLENISHMENT_UOM"].ToString(), (reader["OPT_QTY_LOC_MINIMUM"].ToString() != "") ? decimal.Parse(reader["OPT_QTY_LOC_MINIMUM"].ToString()) : 0M, reader["OPT_LOCATION_TYPE"].ToString(), (reader["OPT_QTY_LOC_LIMIT"].ToString() != "") ? decimal.Parse(reader["OPT_QTY_LOC_LIMIT"].ToString()) : 0M, (reader["SEQ_NO"].ToString() != "") ? int.Parse(reader["SEQ_NO"].ToString()) : 0, reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["STOP"].ToString(), reader["ZONE_ID"].ToString(), reader["FROM_TRANS_ID"].ToString(), reader["TO_TRANS_ID"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["TASK_TYPE_D"].ToString(), reader["STATUS_D"].ToString());
                }
            }
            return taskDetailInfo;
        }

        public TaskDetailInfo GetTaskDetailByID(DataBase dataBase, DbTransaction tran, string taskdetailID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_TASK_DETAIL.Replace("@_@", whLoginID) + " WHERE TASK_DETAIL_ID = @TASK_DETAIL_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TASK_DETAIL_ID", taskdetailID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            TaskDetailInfo taskDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                taskDetailInfo = new TaskDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["TASK_DETAIL_ID"].ToString(), reader["TASK_TYPE"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOT"].ToString(), reader["UOM_ID"].ToString(), (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["FROM_LOC"].ToString(), reader["LOGICAL_FROM_LOC"].ToString(), reader["FROM_LPN_ID"].ToString(), reader["TO_LOC"].ToString(), reader["LOGICAL_TO_LOC"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["PICK_METHOD"].ToString(), reader["STATUS"].ToString(), reader["STATUS_MSG"].ToString(), reader["PRIORITY"].ToString(), reader["SOURCE_PRIORITY"].ToString(), reader["HOLD_ID"].ToString(), reader["USER_ID"].ToString(), reader["USER_POSITION"].ToString(), reader["USER_ID_OVERRIDE"].ToString(), (reader["START_TIME"].ToString() != "") ? DateTime.Parse(reader["START_TIME"].ToString()) : (CS00003 = new DateTime()), (reader["END_TIME"].ToString() != "") ? DateTime.Parse(reader["END_TIME"].ToString()) : (CS00003 = new DateTime()), reader["SOURCE_TYPE"].ToString(), reader["DOC_ID"].ToString(), (reader["DOC_LINE_ID"].ToString() != "") ? int.Parse(reader["DOC_LINE_ID"].ToString()) : 0, reader["PICK_DETAIL_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["LIST_ID"].ToString(), reader["WAVE_ID"].ToString(), reader["REASON_ID"].ToString(), reader["MESSAGE01"].ToString(), reader["MESSAGE02"].ToString(), reader["MESSAGE03"].ToString(), reader["FINAL_TO_LOC"].ToString(), (reader["RELEASE_DATE"].ToString() != "") ? DateTime.Parse(reader["RELEASE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["OPT_BATCH_ID"].ToString(), reader["OPT_TASK_SEQUENCE"].ToString(), reader["OPT_REPLENISHMENT_UOM"].ToString(), (reader["OPT_QTY_LOC_MINIMUM"].ToString() != "") ? decimal.Parse(reader["OPT_QTY_LOC_MINIMUM"].ToString()) : 0M, reader["OPT_LOCATION_TYPE"].ToString(), (reader["OPT_QTY_LOC_LIMIT"].ToString() != "") ? decimal.Parse(reader["OPT_QTY_LOC_LIMIT"].ToString()) : 0M, (reader["SEQ_NO"].ToString() != "") ? int.Parse(reader["SEQ_NO"].ToString()) : 0, reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["STOP"].ToString(), reader["ZONE_ID"].ToString(), reader["FROM_TRANS_ID"].ToString(), reader["TO_TRANS_ID"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["TASK_TYPE_D"].ToString(), reader["STATUS_D"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return taskDetailInfo;
        }

        public DataSet GetTaskDetailByQueryList(List<string> sqlWhere, TaskDetailQueryEntity taskdetailQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_TASK_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!taskdetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, taskdetailQuery.CurrentPage, taskdetailQuery.PageSize, taskdetailQuery.SortField, taskdetailQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetTaskDetailByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, TaskDetailQueryEntity taskdetailQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_TASK_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!taskdetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, taskdetailQuery.CurrentPage, taskdetailQuery.PageSize, taskdetailQuery.SortField, taskdetailQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public TaskDetailInfo GetTaskDetailInfoByDocAndLineID(DataBase dataBase, DbTransaction tran, string docID, string lineID, string taskType, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_TASK_DETAIL.Replace("@_@", whLoginID) + " WHERE DOC_ID = @DOC_ID AND DOC_LINE_ID = @DOC_LINE_ID AND TASK_TYPE = @TASK_TYPE  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DOC_ID", docID), new SqlParameter("@DOC_LINE_ID", lineID), new SqlParameter("@TASK_TYPE", taskType), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            TaskDetailInfo taskDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                taskDetailInfo = new TaskDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["TASK_DETAIL_ID"].ToString(), reader["TASK_TYPE"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOT"].ToString(), reader["UOM_ID"].ToString(), (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["FROM_LOC"].ToString(), reader["LOGICAL_FROM_LOC"].ToString(), reader["FROM_LPN_ID"].ToString(), reader["TO_LOC"].ToString(), reader["LOGICAL_TO_LOC"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["PICK_METHOD"].ToString(), reader["STATUS"].ToString(), reader["STATUS_MSG"].ToString(), reader["PRIORITY"].ToString(), reader["SOURCE_PRIORITY"].ToString(), reader["HOLD_ID"].ToString(), reader["USER_ID"].ToString(), reader["USER_POSITION"].ToString(), reader["USER_ID_OVERRIDE"].ToString(), (reader["START_TIME"].ToString() != "") ? DateTime.Parse(reader["START_TIME"].ToString()) : (CS00003 = new DateTime()), (reader["END_TIME"].ToString() != "") ? DateTime.Parse(reader["END_TIME"].ToString()) : (CS00003 = new DateTime()), reader["SOURCE_TYPE"].ToString(), reader["DOC_ID"].ToString(), (reader["DOC_LINE_ID"].ToString() != "") ? int.Parse(reader["DOC_LINE_ID"].ToString()) : 0, reader["PICK_DETAIL_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["LIST_ID"].ToString(), reader["WAVE_ID"].ToString(), reader["REASON_ID"].ToString(), reader["MESSAGE01"].ToString(), reader["MESSAGE02"].ToString(), reader["MESSAGE03"].ToString(), reader["FINAL_TO_LOC"].ToString(), (reader["RELEASE_DATE"].ToString() != "") ? DateTime.Parse(reader["RELEASE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["OPT_BATCH_ID"].ToString(), reader["OPT_TASK_SEQUENCE"].ToString(), reader["OPT_REPLENISHMENT_UOM"].ToString(), (reader["OPT_QTY_LOC_MINIMUM"].ToString() != "") ? decimal.Parse(reader["OPT_QTY_LOC_MINIMUM"].ToString()) : 0M, reader["OPT_LOCATION_TYPE"].ToString(), (reader["OPT_QTY_LOC_LIMIT"].ToString() != "") ? decimal.Parse(reader["OPT_QTY_LOC_LIMIT"].ToString()) : 0M, (reader["SEQ_NO"].ToString() != "") ? int.Parse(reader["SEQ_NO"].ToString()) : 0, reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["STOP"].ToString(), reader["ZONE_ID"].ToString(), reader["FROM_TRANS_ID"].ToString(), reader["TO_TRANS_ID"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["TASK_TYPE_D"].ToString(), reader["STATUS_D"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return taskDetailInfo;
        }

        public int InsertTaskDetail(TaskDetailInfo taskDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                taskDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_TaskDetail_Parameters(taskDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_TASK_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertTaskDetail(DataBase dataBase, DbTransaction tran, TaskDetailInfo taskDetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                taskDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_TaskDetail_Parameters(taskDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_TASK_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_TaskDetail_Parameters(TaskDetailInfo taskDetailInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.TASK_DETAIL_ID, SqlDbType.VarChar, 20), new SqlParameter(this.TASK_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.LOT, SqlDbType.VarChar, 20), new SqlParameter(this.UOM_ID, SqlDbType.VarChar, 20), new SqlParameter(this.UOM_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.QTY, SqlDbType.Decimal, 13), new SqlParameter(this.FROM_LOC, SqlDbType.VarChar, 20), new SqlParameter(this.LOGICAL_FROM_LOC, SqlDbType.VarChar, 20), new SqlParameter(this.FROM_LPN_ID, SqlDbType.VarChar, 20), new SqlParameter(this.TO_LOC, SqlDbType.VarChar, 20), new SqlParameter(this.LOGICAL_TO_LOC, SqlDbType.VarChar, 20), new SqlParameter(this.TO_LPN_ID, SqlDbType.VarChar, 20), new SqlParameter(this.CASE_ID, SqlDbType.VarChar, 20), 
                new SqlParameter(this.PICK_METHOD, SqlDbType.VarChar, 20), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.STATUS_MSG, SqlDbType.VarChar, 0x100), new SqlParameter(this.PRIORITY, SqlDbType.VarChar, 20), new SqlParameter(this.SOURCE_PRIORITY, SqlDbType.VarChar, 20), new SqlParameter(this.HOLD_ID, SqlDbType.VarChar, 20), new SqlParameter(this.USER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.USER_POSITION, SqlDbType.VarChar, 20), new SqlParameter(this.USER_ID_OVERRIDE, SqlDbType.VarChar, 20), new SqlParameter(this.START_TIME, SqlDbType.DateTime, 8), new SqlParameter(this.END_TIME, SqlDbType.DateTime, 8), new SqlParameter(this.SOURCE_TYPE, SqlDbType.VarChar, 30), new SqlParameter(this.DOC_ID, SqlDbType.VarChar, 20), new SqlParameter(this.DOC_LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.PICK_DETAIL_ID, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_ID, SqlDbType.VarChar, 20), 
                new SqlParameter(this.ORDER_LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.LIST_ID, SqlDbType.VarChar, 20), new SqlParameter(this.WAVE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.REASON_ID, SqlDbType.VarChar, 20), new SqlParameter(this.MESSAGE01, SqlDbType.VarChar, 20), new SqlParameter(this.MESSAGE02, SqlDbType.VarChar, 20), new SqlParameter(this.MESSAGE03, SqlDbType.VarChar, 20), new SqlParameter(this.FINAL_TO_LOC, SqlDbType.VarChar, 20), new SqlParameter(this.RELEASE_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.OPT_BATCH_ID, SqlDbType.VarChar, 20), new SqlParameter(this.OPT_TASK_SEQUENCE, SqlDbType.VarChar, 20), new SqlParameter(this.OPT_REPLENISHMENT_UOM, SqlDbType.VarChar, 20), new SqlParameter(this.OPT_QTY_LOC_MINIMUM, SqlDbType.Decimal, 13), new SqlParameter(this.OPT_LOCATION_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.OPT_QTY_LOC_LIMIT, SqlDbType.Decimal, 13), new SqlParameter(this.SEQ_NO, SqlDbType.Int, 4), 
                new SqlParameter(this.DOOR, SqlDbType.VarChar, 20), new SqlParameter(this.ROUTE, SqlDbType.VarChar, 20), new SqlParameter(this.STOP, SqlDbType.VarChar, 20), new SqlParameter(this.ZONE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.FROM_TRANS_ID, SqlDbType.VarChar, 20), new SqlParameter(this.TO_TRANS_ID, SqlDbType.VarChar, 20), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(taskDetailInfo.WhID))
            {
                paramArray[0].Value = taskDetailInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = taskDetailInfo.TaskDetailID;
            paramArray[2].Value = taskDetailInfo.TaskType;
            paramArray[3].Value = taskDetailInfo.OwnerID;
            paramArray[4].Value = taskDetailInfo.SkuID;
            paramArray[5].Value = taskDetailInfo.Lot;
            paramArray[6].Value = taskDetailInfo.UomID;
            paramArray[7].Value = taskDetailInfo.UomQty;
            paramArray[8].Value = taskDetailInfo.Qty;
            paramArray[9].Value = taskDetailInfo.FromLoc;
            paramArray[10].Value = taskDetailInfo.LogicalFromLoc;
            paramArray[11].Value = taskDetailInfo.FromLpnID;
            paramArray[12].Value = taskDetailInfo.ToLoc;
            paramArray[13].Value = taskDetailInfo.LogicalToLoc;
            paramArray[14].Value = taskDetailInfo.ToLpnID;
            paramArray[15].Value = taskDetailInfo.CaseID;
            paramArray[0x10].Value = taskDetailInfo.PickMethod;
            paramArray[0x11].Value = taskDetailInfo.Status;
            paramArray[0x12].Value = taskDetailInfo.StatusMsg;
            paramArray[0x13].Value = taskDetailInfo.Priority;
            paramArray[20].Value = taskDetailInfo.SourcePriority;
            paramArray[0x15].Value = taskDetailInfo.HoldID;
            paramArray[0x16].Value = taskDetailInfo.UserID;
            paramArray[0x17].Value = taskDetailInfo.UserPosition;
            paramArray[0x18].Value = taskDetailInfo.UserIdOverride;
            paramArray[0x19].Value = taskDetailInfo.StartTime;
            paramArray[0x1a].Value = taskDetailInfo.EndTime;
            paramArray[0x1b].Value = taskDetailInfo.SourceType;
            paramArray[0x1c].Value = taskDetailInfo.DocID;
            paramArray[0x1d].Value = taskDetailInfo.DocLineID;
            paramArray[30].Value = taskDetailInfo.PickDetailID;
            paramArray[0x1f].Value = taskDetailInfo.OrderID;
            paramArray[0x20].Value = taskDetailInfo.OrderLineID;
            paramArray[0x21].Value = taskDetailInfo.ListID;
            paramArray[0x22].Value = taskDetailInfo.WaveID;
            paramArray[0x23].Value = taskDetailInfo.ReasonID;
            paramArray[0x24].Value = taskDetailInfo.Message01;
            paramArray[0x25].Value = taskDetailInfo.Message02;
            paramArray[0x26].Value = taskDetailInfo.Message03;
            paramArray[0x27].Value = taskDetailInfo.FinalToLoc;
            if (taskDetailInfo.ReleaseDate != new DateTime())
            {
                paramArray[40].Value = taskDetailInfo.ReleaseDate;
            }
            else
            {
                paramArray[40].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(taskDetailInfo.OptBatchID))
            {
                paramArray[0x29].Value = taskDetailInfo.OptBatchID;
            }
            else
            {
                paramArray[0x29].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(taskDetailInfo.OptTaskSequence))
            {
                paramArray[0x2a].Value = taskDetailInfo.OptTaskSequence;
            }
            else
            {
                paramArray[0x2a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(taskDetailInfo.OptReplenishmentUom))
            {
                paramArray[0x2b].Value = taskDetailInfo.OptReplenishmentUom;
            }
            else
            {
                paramArray[0x2b].Value = DBNull.Value;
            }
            paramArray[0x2c].Value = taskDetailInfo.OptQtyLocMinimum;
            if (!string.IsNullOrEmpty(taskDetailInfo.OptLocationType))
            {
                paramArray[0x2d].Value = taskDetailInfo.OptLocationType;
            }
            else
            {
                paramArray[0x2d].Value = DBNull.Value;
            }
            paramArray[0x2e].Value = taskDetailInfo.OptQtyLocLimit;
            paramArray[0x2f].Value = taskDetailInfo.SeqNo;
            if (!string.IsNullOrEmpty(taskDetailInfo.Door))
            {
                paramArray[0x30].Value = taskDetailInfo.Door;
            }
            else
            {
                paramArray[0x30].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(taskDetailInfo.Route))
            {
                paramArray[0x31].Value = taskDetailInfo.Route;
            }
            else
            {
                paramArray[0x31].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(taskDetailInfo.Stop))
            {
                paramArray[50].Value = taskDetailInfo.Stop;
            }
            else
            {
                paramArray[50].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(taskDetailInfo.ZoneID))
            {
                paramArray[0x33].Value = taskDetailInfo.ZoneID;
            }
            else
            {
                paramArray[0x33].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(taskDetailInfo.FromTransID))
            {
                paramArray[0x34].Value = taskDetailInfo.FromTransID;
            }
            else
            {
                paramArray[0x34].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(taskDetailInfo.ToTransID))
            {
                paramArray[0x35].Value = taskDetailInfo.ToTransID;
            }
            else
            {
                paramArray[0x35].Value = DBNull.Value;
            }
            paramArray[0x36].Value = taskDetailInfo.CreatedBy;
            paramArray[0x37].Value = taskDetailInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateTaskDetail(TaskDetailInfo taskDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                taskDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_TaskDetail_Parameters(taskDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_TASK_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateTaskDetail(DataBase dataBase, DbTransaction tran, TaskDetailInfo taskDetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                taskDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_TaskDetail_Parameters(taskDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_TASK_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateTaskDetailStatus(DataBase dataBase, DbTransaction tran, string pickDetailID, string taskStatus, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", taskStatus), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@PICK_DETAIL_ID", pickDetailID) };
                if (paras != null)
                {
                    result = dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_TASK_DETAIL_STATUS.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateTaskDetailStatusByDocID(DataBase dataBase, DbTransaction tran, string docID, string taskStatus, string taskType, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", taskStatus), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@DOC_ID", docID), new SqlParameter("@TASK_TYPE", taskType) };
                if (paras != null)
                {
                    result = dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_TASK_DETAIL_STATUS_BY_DOC_ID.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateTaskDetailStatusByDocIDAndLineID(DataBase dataBase, DbTransaction tran, string docID, string lineID, string taskStatus, string taskType, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", taskStatus), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@DOC_ID", docID), new SqlParameter("@DOC_LINE_ID", lineID), new SqlParameter("@TASK_TYPE", taskType) };
                if (paras != null)
                {
                    result = dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_TASK_DETAIL_STATUS_BY_DOC_ID_AND_LINE_ID.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateTaskDetailStatusByPc(DataBase dataBase, DbTransaction tran, string RequestID, string TagNo, string taskStatus, string TaskType, string updateBy, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", taskStatus), new SqlParameter("@UPDATED_BY", updateBy), new SqlParameter("@DOC_ID", RequestID), new SqlParameter("@DOC_LINE_ID", TagNo), new SqlParameter("@TASK_TYPE", TaskType) };
                if (paras != null)
                {
                    result = dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_TASK_DETAIL_STATUS_BY_PC.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

